﻿using eLoan.Models;
using iTextSharp.text;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web.Mvc;
using System.Web.UI.HtmlControls;

namespace eLoan.Controllers
{
    public class ReportController : BaseController
    {

        public ActionResult Index()
        {
            try
            {
                return RedirectToAction("Index", "Home");
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult AgentItinerary(string branchCode, int? agentID, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.AgentItinerary);

                var db = new DatabaseContext();
                var agentItineraryModel = ReportHelper.GetItineraryModel(db, User.Identity.Name, branchCode, agentID, searchText);
                return View(agentItineraryModel);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult RefreshItineraryPDF(AgentItineraryModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.AgentItinerary);

                if (command == AdministrationHelper.DBCommand.Export.ToString() && model.AgentItineraries != null)
                {
                    var table = GetItineraryTable(model);
                    var html = ReportHelper.GetHtmlString(table);
                    string fileName = @"AgentItinerary_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".pdf";
                    string path = ServerBranchReportsPath + fileName;

                    ReportHelper.ExportToPDF(html, path, PageSize.LEGAL.Rotate(), marginLeft: 10f, marginRight: 10f, marginTop: 10f, marginBottom: 10f);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }

                return RedirectToAction("AgentItinerary", "Report", new { branchCode = model.SelectedBranchCode, agentID = model.SelectedAgentID, searchText = model.SearchText });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }

        }

        public ActionResult CollectionReport(string branchCode, bool? filterByAgent, int? agentID,
                                            int? paymentTypeID, DateTime? fromDate, DateTime? toDate, 
                                            bool? isDailyReport, DateTime? date)
        {
            try 
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.CollectionReport);

                var db = new DatabaseContext();
                var collectionModel = ReportHelper.GetCollectionReportModel(db, User.Identity.Name,
                                                                            branchCode, filterByAgent,
                                                                            agentID, paymentTypeID, 
                                                                            fromDate, toDate, isDailyReport, date);
                return View(collectionModel);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }
        
        [HttpPost]
        public ActionResult ExportCollectionToPdf(CollectionReportModel model, string command)
        {
            try 
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.CollectionReport);

                var fileName = @"CollectionReport_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".pdf";
                string path = ServerBranchReportsPath + fileName;

                if (command == AdministrationHelper.DBCommand.Export.ToString() && model.CollectionReportItems != null)
                {
                    var table = GetCollectionTable(model);
                    var html = ReportHelper.GetHtmlString(table);

                    ReportHelper.ExportToPDF(html, path, PageSize.A4);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }
                else if (command == AdministrationHelper.DBCommand.ExportSummary.ToString() && model.CollectionReportItems != null)
                {
                    var table = GetCollectionTable(model, isSummary: true);
                    var html = ReportHelper.GetHtmlString(table);

                    ReportHelper.ExportToPDF(html, path, PageSize.A4);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }
                else if (command == AdministrationHelper.DBCommand.ExportToExcel.ToString() && model.CollectionReportItems != null)
                {
                    var package = GetCollectionExcel(model);
                    byte[] data = package.GetAsByteArray();
                    DownloadFile("CollectionReport.xlsx", data);
                    
                }
                else
                {
                    // refresh
                }

                return RedirectToAction("CollectionReport", "Report",
                    new
                    {
                        branchCode = model.SelectedBranchCode,
                        filterByAgent = model.IsAgentSelected,
                        agentID = model.SelectedAgentID,
                        paymentTypeID = model.SelectedPaymentTypeID,
                        fromDate = model.FromDate,
                        toDate = model.ToDate,
                        isDailyReport = model.IsDailyReport,
                        date = model.Date
                    });

            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult SalesReport(string branchCode, DateTime? fromDate, DateTime? toDate)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.SalesReport);

                var db = new DatabaseContext();
                var salesReportModel = ReportHelper.GetSalesReportModel(db, User.Identity.Name, branchCode, fromDate, toDate);
                return View(salesReportModel);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }
        
        [HttpPost]
        public ActionResult RefreshSales(SalesReportModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.SalesReport);

                if (command == AdministrationHelper.DBCommand.Export.ToString() && model.SalesReportItems != null)
                {
                    var branchCode = AdministrationHelper.GetLoggedInUserBranchCode(new DatabaseContext(), User.Identity.Name);
                    using (ExcelPackage package = new ExcelPackage())
                    {
                        string workBookName = "Sales Report";
                        string centerText = "Sales Report - " + model.FromDate.ToString("MMM/dd/yyyy") + " to " + model.ToDate.ToString("MMM/dd/yyyy");

                        var ws = ReportHelper.SetWorksheet(package, workBookName, centerText);

                        List<string> headerNames = new List<string>();
                        headerNames.Add("Document No");
                        headerNames.Add("Date Released");
                        headerNames.Add("Borrower");
                        headerNames.Add("Agent");
                        headerNames.Add("Loan Type");
                        headerNames.Add("Loan Amount");
                        headerNames.Add("Loan Interest");
                        headerNames.Add("Loan Interest Amount");
                        headerNames.Add("Payment Start Date");
                        headerNames.Add("Payment End Date");
                        headerNames.Add("Loan Status");

                        char columnStart = 'A';
                        int rowStart = 1;
                        ws = ReportHelper.SetWorksheetHeader(ws, headerNames, columnStart, rowStart);

                        var nextRow = ++rowStart;
                        for (var i = 0; i < model.SalesReportItems.Count(); i++)
                        {
                            var nextColumn = columnStart;

                            var headerValues = new List<string>();
                            headerValues.Add(model.SalesReportItems[i].DocumentNo);

                            string dateReleased = "";
                            if (model.SalesReportItems[i].DateReleased.HasValue)
                                dateReleased = model.SalesReportItems[i].DateReleased.GetValueOrDefault().ToString("MMM/dd/yyyy");
                            headerValues.Add(dateReleased);
                            headerValues.Add(model.SalesReportItems[i].BorrowerName);
                            headerValues.Add(model.SalesReportItems[i].AgentName);
                            headerValues.Add(model.SalesReportItems[i].LoanTypeName);
                            headerValues.Add(model.SalesReportItems[i].LoanAmount.ToString("#,###,##0.00"));
                            headerValues.Add(model.SalesReportItems[i].LoanInterest.ToString("#,###,##0.00"));
                            headerValues.Add(model.SalesReportItems[i].LoanInterestAmount.ToString("#,###,##0.00"));

                            if (model.SalesReportItems[i].PaymentStartDate.HasValue)
                            {
                                headerValues.Add(model.SalesReportItems[i].PaymentStartDate.GetValueOrDefault().ToString("MMM/dd/yyyy"));
                            }
                            else
                            {
                                headerValues.Add(model.SalesReportItems[i].PaymentStartDate.GetValueOrDefault().ToString(string.Empty));
                            }

                            if (model.SalesReportItems[i].PaymentEndDate.HasValue)
                            {
                                headerValues.Add(model.SalesReportItems[i].PaymentEndDate.GetValueOrDefault().ToString("MMM/dd/yyyy"));
                            }
                            else
                            {
                                headerValues.Add(string.Empty);
                            }
                            
                            headerValues.Add(model.SalesReportItems[i].LoanStatus);

                            ws = ReportHelper.SetWorksheetRows(ws, headerValues, nextColumn, nextRow);

                            ++nextRow;
                        }

                        var fileName = @"Sales_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                        string path = ServerBranchReportsPath + fileName;
                        var newFileInfo = new FileInfo(path);
                        if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                        package.SaveAs(newFileInfo);
                        ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                    }
                }

                return RedirectToAction("SalesReport", "Report", new
                {
                    branchCode = model.SelectedBranchCode,
                    fromDate = model.FromDate,
                    toDate = model.ToDate
                });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult ChequeReport(string branchCode, DateTime? fromDate, DateTime? toDate, int? loanStatusID, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ChequeReport);

                var db = new DatabaseContext();
                var chequeReportModel = ReportHelper.GetChequeReportModel(db, User.Identity.Name, branchCode, fromDate, toDate, loanStatusID, searchText);
                return View(chequeReportModel);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult RefreshCheques(ChequeReportModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ChequeReport);

                if (command == AdministrationHelper.DBCommand.Export.ToString() && model.ChequeReportItems != null)
                {
                    var branchCode = AdministrationHelper.GetLoggedInUserBranchCode(new DatabaseContext(), User.Identity.Name);
                    using (ExcelPackage package = new ExcelPackage())
                    {
                        string workBookName = "Cheque Report";
                        string centerText = "Cheque Report - " + model.FromDate.ToString("MMM/dd/yyyy") + " to " + model.ToDate.ToString("MMM/dd/yyyy");
                        var ws = ReportHelper.SetWorksheet(package, workBookName, centerText);

                        List<string> headerNames = new List<string>();
                        headerNames.Add("Document No");
                        headerNames.Add("Document Date");
                        headerNames.Add("Borrower");
                        headerNames.Add("Agent");
                        headerNames.Add("Date Printed");
                        headerNames.Add("Printed By");
                        headerNames.Add("Loan Status");
                        headerNames.Add("Cheque No");
                        headerNames.Add("Cheque Amount");
                        headerNames.Add("Loan Amount");
                        headerNames.Add("Loan Interest");
                        headerNames.Add("Total Deduction");
                        headerNames.Add("Total Receivable");

                        char columnStart = 'A';
                        int rowStart = 1;
                        ws = ReportHelper.SetWorksheetHeader(ws, headerNames, columnStart, rowStart);

                        var nextRow = ++rowStart;
                        for (var i = 0; i < model.ChequeReportItems.Count(); i++)
                        {
                            var nextColumn = columnStart;

                            var headerValues = new List<string>();
                            headerValues.Add(model.ChequeReportItems[i].DocumentNo);
                            headerValues.Add(model.ChequeReportItems[i].DocumentDate.ToString("MMM/dd/yyyy"));
                            headerValues.Add(model.ChequeReportItems[i].BorrowerName);
                            headerValues.Add(model.ChequeReportItems[i].AgentName);
                            headerValues.Add(model.ChequeReportItems[i].DatePrinted.ToString("MMM/dd/yyyy"));
                            headerValues.Add(model.ChequeReportItems[i].PrintedBy);
                            headerValues.Add(model.ChequeReportItems[i].LoanStatus);
                            headerValues.Add(model.ChequeReportItems[i].ChequeNo);
                            headerValues.Add(model.ChequeReportItems[i].ChequeAmount.ToString("#,###,##0.00"));
                            headerValues.Add(model.ChequeReportItems[i].LoanAmount.ToString("#,###,##0.00"));
                            headerValues.Add(model.ChequeReportItems[i].LoanInterest.ToString("#,###,##0.00"));
                            headerValues.Add(model.ChequeReportItems[i].TotalDeduction.ToString("#,###,##0.00"));
                            headerValues.Add(model.ChequeReportItems[i].Receivable.ToString("#,###,##0.00"));

                            ws = ReportHelper.SetWorksheetRows(ws, headerValues, nextColumn, nextRow);

                            ++nextRow;
                        }

                        List<string> footer = new List<string>();
                        footer.Add("TOTAL AMOUNT");
                        footer.Add("");
                        footer.Add("");
                        footer.Add("");
                        footer.Add("");
                        footer.Add("");
                        footer.Add("");
                        footer.Add("");
                        footer.Add(model.TotalChequeAmount.ToString("#,###,##0.00"));
                        footer.Add(model.TotalLoanAmount.ToString("#,###,##0.00"));
                        footer.Add("");
                        footer.Add(model.TotalDeduction.ToString("#,###,##0.00"));
                        footer.Add(model.TotalReceivable.ToString("#,###,##0.00"));

                        ws = ReportHelper.SetWorksheetHeader(ws, footer, columnStart, nextRow + 1, setBorder: false);

                        string fileName = @"ChequeReport_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                        string path = ServerBranchReportsPath + fileName;
                        var newFileInfo = new FileInfo(path);
                        if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                        package.SaveAs(newFileInfo);
                        ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                    }
                }

                return RedirectToAction("ChequeReport", "Report", new
                {
                    branchCode = model.SelectedBranchCode,
                    fromDate = model.FromDate,
                    toDate = model.ToDate,
                    loanStatusID = model.SelectedLoanStatus,
                    searchText = model.SearchText
                });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult SavingsReport(string branchCode, DateTime? dateFrom, DateTime? dateTo, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.SavingsReport);

                var db = new DatabaseContext();
                var model = ReportHelper.GetSavingsReportModel(db, User.Identity.Name, branchCode, dateFrom, dateTo, searchText);

                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult ExportSavingsToPdf(SavingsReportModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.SavingsReport);
                var branchCode = AdministrationHelper.GetLoggedInUserBranchCode(new DatabaseContext(), User.Identity.Name);

                if (command == AdministrationHelper.DBCommand.Export.ToString() && model.SavingsReportItems != null)
                {
                    var table = GetSavingsTable(model);
                    var html = ReportHelper.GetHtmlString(table);
                    string fileName = @"SavingsReport_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".pdf";
                    string path = ServerBranchReportsPath + fileName;

                    ReportHelper.ExportToPDF(html, path, PageSize.A4);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }

                return RedirectToAction("SavingsReport", "Report", new
                {
                    branchCode = model.SelectedBranchCode,
                    dateFrom = model.DateFrom,
                    dateTo = model.DateTo,
                    searchText = model.SearchText
                });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult SOAReport(string branchCode, DateTime? fromDate, DateTime? toDate)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.StatementOfAccount);

                var db = new DatabaseContext();
                var model = ReportHelper.GetSOAModel(db, User.Identity.Name, branchCode, fromDate, toDate);
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult RefreshSOA(SOAModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.StatementOfAccount);

                return RedirectToAction("SOAReport", "Report", new
                {
                    branchCode = model.SelectedBranchCode,
                    fromDate = model.FromDate,
                    toDate = model.ToDate
                });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PrintSOA(string documentNo, string branchCode, DateTime? fromDate, DateTime? toDate)
        {
            try 
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.StatementOfAccount);

                var model = ReportHelper.GetPrintSOAModel(documentNo);
                var table = GetSOAItemTable(model);
                var html = ReportHelper.GetHtmlString(table);
                string fileName = @"SOA_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".pdf";
                string path = ServerBranchReportsPath + fileName;
                
                ReportHelper.ExportToPDF(html, path, PageSize.A4);
                ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);

                return RedirectToAction("SOAReport", "Report", new
                {
                    branchCode = branchCode,
                    fromDate = fromDate,
                    toDate = toDate
                });

            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult PenaltyHistory(string branchCode, DateTime? dateFrom, DateTime? dateTo, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PenaltyHistory);

                var db = new DatabaseContext();
                var model = ReportHelper.GetPenaltyHistoryModel(db, branchCode, dateFrom, dateTo, searchText, User.Identity.Name);
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult RefreshPenaltyHistory(PenaltyHistoryModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.PenaltyHistory);
                var branchCode = AdministrationHelper.GetLoggedInUserBranchCode(new DatabaseContext(), User.Identity.Name);

                if (command == AdministrationHelper.DBCommand.Export.ToString())
                {
                    var table = GetPenaltyHistoryTable(model);
                    var html = ReportHelper.GetHtmlString(table);
                    string fileName = @"PenaltyHistory_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".pdf";
                    string path = ServerBranchReportsPath + fileName;

                    ReportHelper.ExportToPDF(html, path, PageSize.A4);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }
                else
                {
                    // refresh
                }

                return RedirectToAction("PenaltyHistory", 
                        new { branchCode = model.SelectedBranchCode, 
                              dateFrom = model.LoanDateFrom, 
                              dateTo = model.LoanDateTo, 
                              searchText = model.SearchText });
            }

            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult ClosedLoansReport(string branchCode, DateTime? dateFrom, DateTime? dateTo, string searchText)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ClosedLoansReport);

                var db = new DatabaseContext();
                var model = ReportHelper.GetClosedLoansReportModel(db, branchCode, dateFrom, dateTo, searchText, User.Identity.Name);
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult RefreshClosedLoans(ClosedLoansReportModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.ClosedLoansReport);
                var branchCode = AdministrationHelper.GetLoggedInUserBranchCode(new DatabaseContext(), User.Identity.Name);

                if (command == AdministrationHelper.DBCommand.Export.ToString())
                {
                    var table = GetClosedLoansTable(model);
                    var html = ReportHelper.GetHtmlString(table);
                    string fileName = @"ClosedLoansReport_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".pdf";
                    string path = ServerBranchReportsPath + fileName;

                    ReportHelper.ExportToPDF(html, path, PageSize.A4, marginLeft: 40f, marginRight: 40f);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }
                else
                {
                    // refresh
                }

                return RedirectToAction("ClosedLoansReport",
                        new
                        {
                            branchCode = model.SelectedBranchCode,
                            dateFrom = model.DateClosedFrom,
                            dateTo = model.DateClosedTo,
                            searchText = model.SearchText
                        });
            }

            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult DuePayments(string branchCode, int? agentID, DateTime? dueDate, string searchText = "")
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DuePayments);

                var db = new DatabaseContext();
                var model = ReportHelper.GetDuePaymentModel(db, branchCode, agentID, searchText, dueDate, User.Identity.Name);
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult DailyDuePayments(string branchCode, int? agentID, DateTime? fromDate, DateTime? toDate, string searchText = "")
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DuePayments);

                var db = new DatabaseContext();
                var model = ReportHelper.GetDailyDuePaymentModel(db, branchCode, agentID, fromDate, toDate, searchText, User.Identity.Name);
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult OtherDuePayments(string branchCode, int? agentID, DateTime? fromDate, DateTime? toDate, string searchText = "")
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DuePayments);

                var db = new DatabaseContext();
                var model = ReportHelper.GetOtherDuePaymentModel(db, branchCode, agentID, fromDate, toDate, searchText, User.Identity.Name);
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult ExportDailyDuePayments(DailyDuePaymentModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DuePayments);

                if (command == AdministrationHelper.DBCommand.ExportDaily.ToString() && model.DailyDuePaymentItems != null)
                {
                    using (ExcelPackage package = new ExcelPackage())
                    {
                        string workBookName = "DuePayment";
                        string centerText = "Due Payment";

                        var ws = ReportHelper.SetWorksheet(package, workBookName, centerText);

                        List<string> headerNames = new List<string>();
                        headerNames.Add("Document No");
                        headerNames.Add("Borrower");
                        headerNames.Add("Payment Frequency");
                        headerNames.Add("Amount Due");
                        headerNames.Add("Amount Paid");
                        headerNames.Add("Payment Type");
                        headerNames.Add("Contact Numbers");
                        headerNames.Add("Remarks");

                        char columnStart = 'A';
                        int rowStart = 1;
                        ws = ReportHelper.SetWorksheetHeader(ws, headerNames, columnStart, rowStart);

                        var nextRow = ++rowStart;
                        for (var i = 0; i < model.DailyDuePaymentItems.Count(); i++)
                        {
                            var nextColumn = columnStart;

                            var headerValues = new List<string>();
                            headerValues.Add(model.DailyDuePaymentItems[i].DocumentNo);
                            headerValues.Add(model.DailyDuePaymentItems[i].BorrowerName);
                            headerValues.Add(model.DailyDuePaymentItems[i].PaymentFrequency);
                            headerValues.Add(model.DailyDuePaymentItems[i].DueAmount.ToString("#,###,##0.00"));
                            headerValues.Add(model.DailyDuePaymentItems[i].AmountPaid.ToString("#,###,##0.00"));
                            headerValues.Add(model.DailyDuePaymentItems[i].PaymentTypeName);
                            headerValues.Add(model.DailyDuePaymentItems[i].ContactNumbers);
                            headerValues.Add(model.DailyDuePaymentItems[i].DueRemarks);

                            ws = ReportHelper.SetWorksheetRows(ws, headerValues, nextColumn, nextRow);

                            ++nextRow;
                        }

                        string fileName = @"DuePayment_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                        string path = ServerAdminReportsPath + fileName;
                        var newFileInfo = new FileInfo(path);
                        if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                        package.SaveAs(newFileInfo);
                        ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                    }
                }
                else if (command == AdministrationHelper.DBCommand.SaveDaily.ToString() && model.DailyDuePaymentItems != null)
                {
                    var db = new DatabaseContext();

                    foreach (var item in model.DailyDuePaymentItems)
                    {
                        if (item.ScheduleID > 0)
                        {
                            var sched = db.LoanPaymentSchedules.Where(w => w.ID == item.ScheduleID).SingleOrDefault();
                            sched.DueRemarks = item.DueRemarks;
                            db.Entry(sched).State = System.Data.EntityState.Modified;
                            db.SaveChanges();
                        }

                        if (item.PaymentID > 0)
                        {
                            var payment = db.LoanPayments.Where(w => w.ID == item.PaymentID).SingleOrDefault();
                            payment.DueRemarks = item.DueRemarks;
                            db.Entry(payment).State = System.Data.EntityState.Modified;
                            db.SaveChanges();
                        }
                    }
                }

                return RedirectToAction("DailyDuePayments", "Report",
                    new
                    {
                        branchCode = model.SelectedBranchCode,
                        agentID = model.SelectedAgentID,
                        fromDate = model.FromDate,
                        toDate = model.ToDate,
                        searchText = model.SearchText
                    });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult ExportOtherDuePayments(OtherDuePaymentModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DuePayments);

                if (command == AdministrationHelper.DBCommand.ExportOthers.ToString() && model.OtherDuePaymentItems != null)
                {
                    using (ExcelPackage package = new ExcelPackage())
                    {
                        string workBookName = "DuePayment";
                        string centerText = "Due Payment";

                        var ws = ReportHelper.SetWorksheet(package, workBookName, centerText);

                        List<string> headerNames = new List<string>();
                        headerNames.Add("Document No");
                        headerNames.Add("Borrower");
                        headerNames.Add("Payment Frequency");
                        headerNames.Add("Amount Due");
                        headerNames.Add("Amount Paid");
                        headerNames.Add("Payment Type");
                        headerNames.Add("Contact Numbers");
                        headerNames.Add("Remarks");

                        char columnStart = 'A';
                        int rowStart = 1;
                        ws = ReportHelper.SetWorksheetHeader(ws, headerNames, columnStart, rowStart);

                        var nextRow = ++rowStart;
                        for (var i = 0; i < model.OtherDuePaymentItems.Count(); i++)
                        {
                            var nextColumn = columnStart;

                            var headerValues = new List<string>();
                            headerValues.Add(model.OtherDuePaymentItems[i].DocumentNo);
                            headerValues.Add(model.OtherDuePaymentItems[i].BorrowerName);
                            headerValues.Add(model.OtherDuePaymentItems[i].PaymentFrequency);
                            headerValues.Add(model.OtherDuePaymentItems[i].DueAmount.ToString("#,###,##0.00"));
                            headerValues.Add(model.OtherDuePaymentItems[i].AmountPaid.ToString("#,###,##0.00"));
                            headerValues.Add(model.OtherDuePaymentItems[i].PaymentTypeName);
                            headerValues.Add(model.OtherDuePaymentItems[i].ContactNumbers);
                            headerValues.Add(model.OtherDuePaymentItems[i].DueRemarks);

                            ws = ReportHelper.SetWorksheetRows(ws, headerValues, nextColumn, nextRow);

                            ++nextRow;
                        }

                        string fileName = @"DuePayment_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".xlsx";
                        string path = ServerAdminReportsPath + fileName;
                        var newFileInfo = new FileInfo(path);
                        if (!newFileInfo.Directory.Exists) Directory.CreateDirectory(newFileInfo.Directory.FullName);

                        package.SaveAs(newFileInfo);
                        ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                    }
                }
                else if (command == AdministrationHelper.DBCommand.SaveOthers.ToString() && model.OtherDuePaymentItems != null)
                {
                    var db = new DatabaseContext();

                    foreach (var item in model.OtherDuePaymentItems)
                    {
                        if (item.ScheduleID > 0)
                        {
                            var sched = db.LoanPaymentSchedules.Where(w => w.ID == item.ScheduleID).SingleOrDefault();
                            sched.DueRemarks = item.DueRemarks;
                            db.Entry(sched).State = System.Data.EntityState.Modified;
                            db.SaveChanges();
                        }

                        if (item.PaymentID > 0)
                        {
                            var payment = db.LoanPayments.Where(w => w.ID == item.PaymentID).SingleOrDefault();
                            payment.DueRemarks = item.DueRemarks;
                            db.Entry(payment).State = System.Data.EntityState.Modified;
                            db.SaveChanges();
                        }

                    }
                }

                return RedirectToAction("OtherDuePayments", "Report",
                    new
                    {
                        branchCode = model.SelectedBranchCode,
                        agentID = model.SelectedAgentID,
                        fromDate = model.FromDate,
                        toDate = model.ToDate,
                        searchText = model.SearchText
                    });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult CollectionReconciliationReport(string branchCode, int? agentID, DateTime? collectionDate)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DailyCollectionReconciliationReport);

                var db = new DatabaseContext();
                var model = ReportHelper.GetCollectionReconciliationReportModel(db, branchCode, agentID, collectionDate, User.Identity.Name);

                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult RefreshCollectionReconciliationReport(CollectionReconciliationReportModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DailyCollectionReconciliationReport);

                if (command == AdministrationHelper.DBCommand.Export.ToString() && model.CollectionOnRecordItems != null && model.CollectionReconciliationItems != null)
                {
                    var table = GetCollectionReconciliationTable(model);
                    var html = ReportHelper.GetHtmlString(table);
                    string fileName = @"DCR_" + string.Format("{0:ddMMMyyy}", DateTime.Now) + "_" + DateTime.Now.Second.ToString() + ".pdf";
                    string path = SelectedBranchReportsPath(model.SelectedBranchCode) + fileName;

                    ReportHelper.ExportToPDF(html, path, PageSize.LEGAL.Rotate(), marginLeft: 10f, marginRight: 10f, marginTop: 10f, marginBottom: 10f);
                    ReportHelper.DownloadFile(ServerReportsUrlPath, ClientReportsPath, fileName);
                }

                return RedirectToAction("CollectionReconciliationReport", new { branchCode = model.SelectedBranchCode, agentID = model.SelectedAgentID, collectionDate = model.CollectionDate });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult CollectionReconciliationReportEdit(int? id)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DailyCollectionReconciliation);

                var db = new DatabaseContext();

                var model = new CollectionReconciliationEditModel();
                model.CollectionReconciliationItem = db.CollectionReconciliations.Where(w => w.ID == id).SingleOrDefault<CollectionReconciliation>();
                model.AgentName = AdministrationHelper.GetAgentName(model.CollectionReconciliationItem.AgentID);
                model.BranchName = AdministrationHelper.GetBranchName(model.CollectionReconciliationItem.BranchCode);

                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        [HttpPost]
        public ActionResult CollectionReconciliationReportEdit(CollectionReconciliationEditModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DailyCollectionReconciliation);

                using (var db = new DatabaseContext())
                {
                    if (command == AdministrationHelper.DBCommand.Save.ToString())
                    {
                        model.CollectionReconciliationItem.StatusID = (int)AdministrationHelper.CollectionReconciliationStatus.Reviewed;
                        db.Entry(model.CollectionReconciliationItem).State = System.Data.EntityState.Modified;
                        db.SaveChanges();
                    }
                    else
                    {
                        // cancelled
                    }
                }

                return RedirectToAction("CollectionReconciliationReport", new { branchCode = model.CollectionReconciliationItem.BranchCode, agentID = 0, collectionDate = model.CollectionReconciliationItem.Date });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult AgingReport(string branchCode, int? agentID, DateTime? fromDate, DateTime? toDate, int days = 0)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.DailyCollectionReconciliation);

                var db = new DatabaseContext();
                var model = ReportHelper.GetAgingReportModel(db, branchCode, agentID, fromDate, toDate, User.Identity.Name, days);
                return View(model);
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public ActionResult ExportAgingReport(AgingReportModel model, string command)
        {
            try
            {
                CheckUserPageAccess(AdministrationHelper.UserPageAccess.AgingReport);

                if (command == AdministrationHelper.DBCommand.ExportToExcel.ToString())
                {
                    var package = GetAgingExcel(model);
                    byte[] data = package.GetAsByteArray();
                    DownloadFile("AgingReport.xlsx", data);
                }
                else if (command == AdministrationHelper.DBCommand.ExportToPdf.ToString())
                {
                    var data = GetAgingPdf(model);
                    this.DownloadFile(@"AgingReport.pdf", data);
                }

                return RedirectToAction("AgingReport", new
                {
                    branchCode = model.SelectedBranchCode,
                    agentID = model.SelectedAgentID,
                    fromDate = model.FromDate,
                    toDate = model.ToDate,
                    days = model.SelectedDays
                });
            }
            catch (Exception ex)
            {
                return View("Error", ex);
            }
        }

        public byte[] GetAgingPdf(AgingReportModel model)
        {
            var file = Server.MapPath("~/Documents/agingreport.html");
            var html = System.IO.File.ReadAllText(file);

            string htmlString = string.Empty;
            foreach (var item in model.AgingItems)
            {
                var itemString = "<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>";

                itemString = itemString.Replace("{0}", item.BorrowerName);
                itemString = itemString.Replace("{1}", item.DocumentNo);
                itemString = itemString.Replace("{2}", item.LoanAmount.ToString("###,###,##0.00"));
                itemString = itemString.Replace("{3}", item.LoanType);

                htmlString = htmlString + itemString;
            }

            html = html.Replace("{0}", htmlString);

            var logoFile = Server.MapPath(@"~/Documents/eqglogo2.png");
            byte[] logo = System.IO.File.ReadAllBytes(logoFile);

            byte[] data = ReportHelper.ExportToPDF(html, logo, PageSize.LETTER, marginTop: 40f, marginLeft: 80f, marginRight: 90f);

            return data;
        }

        private ExcelPackage GetAgingExcel(AgingReportModel model)
        {
            var fileInfo = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + @"Documents\AgingReport.xlsx");
            var package = new ExcelPackage(fileInfo);

            var ws = package.Workbook.Worksheets["Report"];

            int start = 4;
            for (var i = 0; i < model.AgingItems.Count; i++)
            {
                ws.Cells["A" + start.ToString()].Value = model.AgingItems[i].BorrowerName;
                ws.Cells["B" + start.ToString()].Value = model.AgingItems[i].DocumentNo;
                ws.Cells["C" + start.ToString()].Value = model.AgingItems[i].LoanAmount.ToString("###,###,##0.00");
                ws.Cells["D" + start.ToString()].Value = model.AgingItems[i].LoanType;

                start++;
            }


            return package;
        }

        #region Methods  / Functions

        private HtmlTable GetItineraryTable(AgentItineraryModel model)
        {
            model.AgentItineraries = model.AgentItineraries.OrderBy(m => m.BorrowerName).ToList();
            model.SelectedAgentName = AdministrationHelper.GetAgentName(model.SelectedAgentID);

            var table = ReportHelper.GetTable(setBorder: false);

            table = ReportHelper.SetHeaderValues(table, new HtmlTableRow(), new List<string>() { model.SelectedAgentName + " " + DateTime.Now.ToString("MMM/dd/yyyy") + " Itinerary" }, columnSpan: 9, textAlign: "center");
            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { "" }, setBorder: false, columnSpan: 9);

            var totalRow = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, totalRow, new List<string>() { "Total no. of borrowers: " + model.AgentItineraries.Count().ToString() }, columnSpan: 9, setBorder: false);

            List<string> headerValues = new List<string>();
            //headerValues.Add("Borrower");
            headerValues.Add("Contact Numbers");
            headerValues.Add("Maturity Date");
            headerValues.Add("Outstanding Balance");
            headerValues.Add("Penalty Balance");
            headerValues.Add("Weekly Payment");
            headerValues.Add("Short / Over");
            headerValues.Add("Amount Paid");
            //headerValues.Add("Signature");

            var headerRow = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Borrower" }, width: "15%");
            table = ReportHelper.SetHeaderValues(table, headerRow, headerValues, width: "10%");
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Signature" }, width: "15%");
            
            for (var i = 0; i < model.AgentItineraries.Count(); i++)
            {
                var rowValues = new List<string>();
                rowValues.Add(model.AgentItineraries[i].BorrowerName);
                rowValues.Add(model.AgentItineraries[i].ContactNumbers);

                if (model.AgentItineraries[i].PaymentEndDate.HasValue)
                {
                    rowValues.Add(model.AgentItineraries[i].PaymentEndDate.GetValueOrDefault().ToString("MMM/dd/yyyy"));
                }
                else
                {
                    rowValues.Add(string.Empty);
                }

                rowValues.Add(model.AgentItineraries[i].OutstandingBalance.ToString("#,###,##0.00"));
                rowValues.Add(model.AgentItineraries[i].PenaltyBalance.ToString("#,###,##0.00"));
                rowValues.Add(model.AgentItineraries[i].WeeklyPayment.ToString("#,###,##0.00"));
                rowValues.Add(model.AgentItineraries[i].ShortOver.ToString("#,###,##0.00"));
                rowValues.Add(string.Empty);
                rowValues.Add(string.Empty);
                
                table = ReportHelper.SetRowValues(table, new HtmlTableRow(), rowValues);

            }

            return table;
        }

        private HtmlTable GetSOAItemTable(PrintSOAModel model)
        {
            var table = ReportHelper.GetTable(setBorder: false);

            var headerValues = new List<string>();
            headerValues.Add("STATEMENT OF ACCOUNT");

            var header = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, header, headerValues, columnSpan: 7, textAlign: "center");

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { "" }, columnSpan: 7, setBorder: false);

            var row1 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, row1, new List<string>() { "Borrower" });
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { model.BorrowerName }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetHeaderValues(table, row1, new List<string>() { "Total Savings" });
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { model.TotalSavings.ToString("#,###,##0.00") }, columnSpan: 2, textAlign: "right");

            var row2 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, row2, new List<string>() { "Document No." });
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { model.DocumentNo }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { "" }, setBorder: false);
            table = ReportHelper.SetHeaderValues(table, row2, new List<string>() { "Receivable" });
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { model.Receivable.ToString("#,###,##0.00") }, columnSpan: 2, textAlign: "right");

            var row3 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, row3, new List<string>() { "Payment Start Date" });

            var startDate = model.PaymentStartDate.HasValue ? model.PaymentStartDate.GetValueOrDefault().ToString("MMM/dd/yyyy") : string.Empty;

            table = ReportHelper.SetRowValues(table, row3, new List<string>() { startDate }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row3, new List<string>() { "" }, setBorder: false);
            table = ReportHelper.SetHeaderValues(table, row3, new List<string>() { "Outstanding Balance" });
            table = ReportHelper.SetRowValues(table, row3, new List<string>() { model.OutstandingBalance.ToString("#,###,##0.00") }, columnSpan: 2, textAlign: "right");

            var row4 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, row4, new List<string>() { "Loan Status" });
            table = ReportHelper.SetRowValues(table, row4, new List<string>() { model.LoanStatus }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row4, new List<string>() { "" }, setBorder: false);
            table = ReportHelper.SetHeaderValues(table, row4, new List<string>() { "Penalty Balance" });
            table = ReportHelper.SetRowValues(table, row4, new List<string>() { model.PenaltyBalance.ToString("#,###,##0.00") }, columnSpan: 2, textAlign: "right");

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { "" }, columnSpan: 7, setBorder: false);

            var paymentHeaderValues = new List<string>();
            paymentHeaderValues.Add("Due Date");
            paymentHeaderValues.Add("Due Amount");
            paymentHeaderValues.Add("Payment Date");
            paymentHeaderValues.Add("Paid Amount");
            paymentHeaderValues.Add("Paid Penalty");
            paymentHeaderValues.Add("Payment Type");
            paymentHeaderValues.Add("Payment Status");

            var paymentHeader = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, paymentHeader, paymentHeaderValues);

            for (var i = 0; i < model.Payments.Count(); i++)
            {
                var paymentRow = new HtmlTableRow();
                var paymentRowValues = new List<string>();
                paymentRowValues.Add(model.Payments[i].DueDate.ToString("MMM/dd/yyyy"));
                paymentRowValues.Add(model.Payments[i].DueAmount.ToString("#,###,##0.00"));

                if (model.Payments[i].PaymentDate > model.DocumentDate)
                    paymentRowValues.Add(model.Payments[i].PaymentDate.GetValueOrDefault().ToString("MMM/dd/yyyy"));
                else
                    paymentRowValues.Add(string.Empty);

                paymentRowValues.Add(model.Payments[i].PaidAmount.ToString("#,###,##0.00"));
                paymentRowValues.Add(model.Payments[i].Penalty.ToString("#,###,##0.00"));
                paymentRowValues.Add(model.Payments[i].PaymentType.ToUpper());
                paymentRowValues.Add(model.Payments[i].PaymentStatus.ToUpper());
                
                table = ReportHelper.SetRowValues(table, paymentRow, paymentRowValues);

            }

            if (!model.Payments.Any())
                table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { "-- No Payments. --" }, columnSpan: 6, textAlign: "center");

            return table;
        }

        private HtmlTable GetCollectionTable(CollectionReportModel model, bool isSummary = false)
        {
            model.CollectionReportItems = model.CollectionReportItems.OrderBy(o => o.AgentName).ThenBy(t => t.BorrowerName).ToList();

            var table = ReportHelper.GetTable(setBorder: false);

            int colSpan = isSummary ? 4 : 5;
            table = ReportHelper.SetHeaderValues(table, new HtmlTableRow(), new List<string>() { "DAILY COLLECTION REPORT" }, columnSpan: colSpan, textAlign: "center");
            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: colSpan, setBorder: false);

            var rowH1 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, rowH1, new List<string>() { "Branch" });

            colSpan = isSummary ? 3 : 4;
            table = ReportHelper.SetRowValues(table, rowH1, new List<string>() { AdministrationHelper.GetBranchName(model.SelectedBranchCode) }, columnSpan: colSpan, textAlign: "right");

            var rowH2 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, rowH2, new List<string>() { "Date" });

            string dateValue = model.ToDate.ToString("MMM/dd/yyyy");
            if (!model.FromDate.Equals(model.ToDate))
                dateValue = model.FromDate.ToString("MMM/dd/yyyy") + "  to  " + model.ToDate.ToString("MMM/dd/yyyy");

            table = ReportHelper.SetRowValues(table, rowH2, new List<string>() { dateValue }, columnSpan: colSpan, textAlign: "right");
            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: colSpan, setBorder: false);


            var headerValues = new List<string>();
            headerValues.Add("Agent");
            if (!isSummary) headerValues.Add("Borrower");
            headerValues.Add("Penalty");
            headerValues.Add("Loan Payment");
            headerValues.Add("Total");

            var header = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, header, headerValues);

            decimal total = 0;

            if (isSummary)
            {
                var distinctAgents = model.CollectionReportItems.Select(s => new KeyValuePair<int,string>(s.AgentID, s.AgentName)).Distinct().ToList();

                
                foreach (var agent in distinctAgents)
                {
                    var row = new HtmlTableRow();
                    
                    decimal totalPostedPayment = 0;
                    decimal totalPenalty = 0;

                    var list = model.CollectionReportItems.Where(w => w.AgentID == agent.Key);

                    if (list.Any())
                    { 
                        totalPostedPayment = list.Sum(s => s.PostedPayment);
                        totalPenalty = list.Sum(s => s.Penalty);
                    }

                    total = totalPostedPayment + totalPenalty;

                    table = ReportHelper.SetRowValues(table, row, new List<string>() { agent.Value });
                    table = ReportHelper.SetRowValues(table, row, new List<string>() { totalPenalty.ToString("#,###,##0.00") }, textAlign: "right");
                    table = ReportHelper.SetRowValues(table, row, new List<string>() { totalPostedPayment.ToString("#,###,##0.00") }, textAlign: "right");
                    table = ReportHelper.SetRowValues(table, row, new List<string>() { total.ToString("#,###,##0.00") }, textAlign: "right");
                }
            }
            else
            {
                for (var i = 0; i < model.CollectionReportItems.Count(); i++)
                {
                    var row = new HtmlTableRow();
                    
                    table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReportItems[i].AgentName });
                    table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReportItems[i].BorrowerName });
                    table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReportItems[i].Penalty.ToString("#,###,##0.00") }, textAlign: "right");
                    table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReportItems[i].PostedPayment.ToString("#,###,##0.00") }, textAlign: "right");

                    total = model.CollectionReportItems[i].Penalty + model.CollectionReportItems[i].PostedPayment;
                    table = ReportHelper.SetRowValues(table, row, new List<string>() { total.ToString("#,###,##0.00") }, textAlign: "right");
                }
            }

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 4, setBorder: false);

            var row1 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { string.Empty }, setBorder: false);
            if (!isSummary) table = ReportHelper.SetRowValues(table, row1, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetHeaderValues(table, row1, new List<string>() { "Total Penalty" });
            table = ReportHelper.SetHeaderValues(table, row1, new List<string>() { "Total Loan Payment" });
            table = ReportHelper.SetHeaderValues(table, row1, new List<string>() { "TOTAL COLLECTION" });

            var row2 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { string.Empty }, setBorder: false);
            if (!isSummary) table = ReportHelper.SetRowValues(table, row2, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { model.TotalPenalty.ToString("#,###,##0.00") }, textAlign: "right");
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { model.TotalPayment.ToString("#,###,##0.00") }, textAlign: "right");
            table = ReportHelper.SetHeaderValues(table, row2, new List<string>() { model.TotalCollection.ToString("#,###,##0.00") }, textAlign: "right");

            return table;
        }

        private ExcelPackage GetCollectionExcel(CollectionReportModel model)
        {
            var fileInfo = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + @"Documents\CollectionReport.xlsx");
            var package = new ExcelPackage(fileInfo);

            var ws = package.Workbook.Worksheets["Report"];

            ws.Cells["B2"].Value = AdministrationHelper.GetBranchName(model.SelectedBranchCode);
            ws.Cells["B3"].Value = model.FromDate.ToString("MMM/dd/yyyy") + " to " + model.ToDate.ToString("MMM/dd/yyyy");
            ws.Cells["B4"].Value = model.TotalPenalty.ToString("###,###,##0.00");
            ws.Cells["B5"].Value = model.TotalPayment.ToString("###,###,##0.00");
            ws.Cells["B6"].Value = model.TotalCollection.ToString("###,###,##0.00");
            
            int start = 9;
            for (var i = 0; i < model.CollectionReportItems.Count; i++)
            {
                ws.Cells["A" + start.ToString()].Value = model.CollectionReportItems[i].AgentName;
                ws.Cells["B" + start.ToString()].Value = model.CollectionReportItems[i].BorrowerName;
                ws.Cells["C" + start.ToString()].Value = model.CollectionReportItems[i].Penalty.ToString("###,###,##0.00");
                ws.Cells["D" + start.ToString()].Value = model.CollectionReportItems[i].PostedPayment.ToString("###,###,##0.00");

                decimal total = model.CollectionReportItems[i].Penalty + model.CollectionReportItems[i].PostedPayment;

                ws.Cells["E" + start.ToString()].Value = total.ToString("###,###,##0.00");

                start++;
            }


            return package;
        }
            
        private HtmlTable GetSavingsTable(SavingsReportModel model)
        {
            var table = ReportHelper.GetTable(setBorder: false);

            var mainHeaderValues = new List<string>();
            mainHeaderValues.Add("SAVINGS REPORT");

            var mainHeader = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, mainHeader, mainHeaderValues, columnSpan: 5, textAlign: "center");
            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 5, setBorder: false);

            var rowH1 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, rowH1, new List<string>() { "Branch" });
            table = ReportHelper.SetRowValues(table, rowH1, new List<string>() { AdministrationHelper.GetBranchName(model.SelectedBranchCode) }, columnSpan: 4, textAlign: "right");

            var rowH2 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, rowH2, new List<string>() { "Date" });
            table = ReportHelper.SetRowValues(table, rowH2, new List<string>() { model.DateFrom.ToString("MMM/dd/yyyy") + " to " + model.DateTo.ToString("MMM/dd/yyyy") }, columnSpan: 4, textAlign: "right");

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 5, setBorder: false);

            var headerValues = new List<string>();
            headerValues.Add("Borrower");
            headerValues.Add("Remarks");
            headerValues.Add("Date Created");
            headerValues.Add("Created By");
            headerValues.Add("Amount");

            var header = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, header, headerValues);

            for (var i = 0; i < model.SavingsReportItems.Count(); i++)
            {
                var row = new HtmlTableRow();
                var rowValues = new List<string>();
                rowValues.Add(model.SavingsReportItems[i].BorrowerName);
                rowValues.Add(model.SavingsReportItems[i].Remarks);
                rowValues.Add(model.SavingsReportItems[i].DateCreated.ToString("MMM/dd/yyyy"));
                rowValues.Add(model.SavingsReportItems[i].CreatedBy);
                rowValues.Add(model.SavingsReportItems[i].Amount.ToString("#,###,##0.00"));

                table = ReportHelper.SetRowValues(table, row, rowValues);

            }

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 3, setBorder: false);

            var row1 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetHeaderValues(table, row1, new List<string>() { "Total Amount" });

            var row2 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { model.TotalSavings.ToString("#,###,##0.00") });

            return table;
        }

        private HtmlTable GetPenaltyHistoryTable(PenaltyHistoryModel model)
        {
            model.PenaltyItems = model.PenaltyItems.OrderBy(o => o.BorrowerName).ThenBy(t => t.DocumentNo).ThenBy(t => t.PenaltyDate).ToList();
            model.PenaltyPaymentItems = model.PenaltyPaymentItems.OrderBy(o => o.BorrowerName).ThenBy(t => t.DocumentNo).ThenBy(t => t.PaymentDate).ToList();

            var table = ReportHelper.GetTable(setBorder: false);

            var mainHeaderValues = new List<string>();
            mainHeaderValues.Add("PENALTY HISTORY");

            var mainHeader = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, mainHeader, mainHeaderValues, columnSpan: 6, textAlign: "center");
            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 6, setBorder: false);

            var rowH1 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, rowH1, new List<string>() { "Branch" }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, rowH1, new List<string>() { AdministrationHelper.GetBranchName(model.SelectedBranchCode) }, columnSpan: 4, textAlign: "right");

            var rowH2 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, rowH2, new List<string>() { "Date" }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, rowH2, new List<string>() { model.LoanDateFrom.ToString("MMM/dd/yyyy") + " to " + model.LoanDateTo.ToString("MMM/dd/yyyy") }, columnSpan: 4, textAlign: "right");

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 6, setBorder: false);

            // PENALTIES

            var headerValues = new List<string>();
            headerValues.Add("Borrower");
            headerValues.Add("Document No");
            headerValues.Add("Loan Date");
            headerValues.Add("Penalty Date");
            headerValues.Add("Remarks");
            headerValues.Add("Penalty");

            var header = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, header, headerValues);

            for (var i = 0; i < model.PenaltyItems.Count(); i++)
            {
                var row = new HtmlTableRow();
                var rowValues = new List<string>();
                rowValues.Add(model.PenaltyItems[i].BorrowerName);
                rowValues.Add(model.PenaltyItems[i].DocumentNo);
                rowValues.Add(model.PenaltyItems[i].DocumentDate.ToString("MMM/dd/yyyy"));
                rowValues.Add(model.PenaltyItems[i].PenaltyDate.ToString("MMM/dd/yyyy"));
                rowValues.Add(model.PenaltyItems[i].Remarks);

                table = ReportHelper.SetRowValues(table, row, rowValues);
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.PenaltyItems[i].Penalty.ToString("#,###,##0.00") }, textAlign: "right");
            }

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 6, setBorder: false);

            var row1 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row1, new List<string>() { string.Empty, string.Empty, string.Empty, string.Empty, string.Empty }, setBorder: false);
            table = ReportHelper.SetHeaderValues(table, row1, new List<string>() { "Total Penalty" });

            var row2 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { string.Empty, string.Empty, string.Empty, string.Empty, string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row2, new List<string>() { model.TotalPenalty.ToString("#,###,##0.00") }, textAlign: "right");

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 6, setBorder: false);

            // PENALTY PAYMENTS

            var headerValues2 = new List<string>();
            headerValues2.Add("Borrower");
            headerValues2.Add("Document No");
            headerValues2.Add("Loan Date");
            headerValues2.Add("Payment Date");

            var header2 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, header2, headerValues2);
            table = ReportHelper.SetHeaderValues(table, header2, new List<string>() { "Paid Penalty Amount" }, columnSpan: 2);

            for (var i = 0; i < model.PenaltyPaymentItems.Count(); i++)
            {
                var row3 = new HtmlTableRow();
                var rowValues3 = new List<string>();
                rowValues3.Add(model.PenaltyPaymentItems[i].BorrowerName);
                rowValues3.Add(model.PenaltyPaymentItems[i].DocumentNo);
                rowValues3.Add(model.PenaltyPaymentItems[i].DocumentDate.ToString("MMM/dd/yyyy"));
                rowValues3.Add(model.PenaltyPaymentItems[i].PaymentDate.GetValueOrDefault().ToString("MMM/dd/yyyy"));

                table = ReportHelper.SetRowValues(table, row3, rowValues3);
                table = ReportHelper.SetRowValues(table, row3, new List<string>() { model.PenaltyPaymentItems[i].PaidPenaltyAmount.ToString("#,###,##0.00") }, columnSpan: 2, textAlign: "right");

            }

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 5, setBorder: false);

            var row4 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row4, new List<string>() { string.Empty, string.Empty, string.Empty, string.Empty }, setBorder: false);
            table = ReportHelper.SetHeaderValues(table, row4, new List<string>() { "Total Paid Penalty" }, columnSpan: 2);

            var row5 = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row5, new List<string>() { string.Empty, string.Empty, string.Empty, string.Empty }, setBorder: false);
            table = ReportHelper.SetRowValues(table, row5, new List<string>() { model.TotalPenaltyPayment.ToString("#,###,##0.00") }, columnSpan: 2, textAlign: "right");

            return table;
        }

        private HtmlTable GetClosedLoansTable(ClosedLoansReportModel model)
        {
            model.ClosedLoanReportItems = model.ClosedLoanReportItems.OrderBy(o => o.BorrowerName).ThenBy(t => t.DocumentNo).ThenBy(t => t.DocumentDate).ThenBy(t => t.AgentName).ToList();

            var table = ReportHelper.GetTable(setBorder: false);

            var mainHeaderValues = new List<string>();
            mainHeaderValues.Add("CLOSED LOANS REPORT");

            var mainHeader = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, mainHeader, mainHeaderValues, columnSpan: 9, textAlign: "center");
            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 9, setBorder: false);

            var rowH1 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, rowH1, new List<string>() { "Branch" }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, rowH1, new List<string>() { AdministrationHelper.GetBranchName(model.SelectedBranchCode) }, columnSpan: 7, textAlign: "right");

            var rowH2 = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, rowH2, new List<string>() { "Date" }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, rowH2, new List<string>() { model.DateClosedFrom.ToString("MMM/dd/yyyy") + " to " + model.DateClosedTo.ToString("MMM/dd/yyyy") }, columnSpan: 7, textAlign: "right");

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 9, setBorder: false);

            var headerValues = new List<string>();
            headerValues.Add("Borrower");
            headerValues.Add("Contact No");
            headerValues.Add("Document No");
            headerValues.Add("Loan Date");
            headerValues.Add("Agent");
            headerValues.Add("Remarks");
            headerValues.Add("Status");
            headerValues.Add("Receivable");
            headerValues.Add("Total Payment");

            var header = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, header, headerValues);

            for (var i = 0; i < model.ClosedLoanReportItems.Count(); i++)
            {
                var row = new HtmlTableRow();
                var rowValues = new List<string>();

                rowValues.Add(model.ClosedLoanReportItems[i].BorrowerName);
                rowValues.Add(model.ClosedLoanReportItems[i].ContactNo);
                rowValues.Add(model.ClosedLoanReportItems[i].DocumentNo);
                rowValues.Add(model.ClosedLoanReportItems[i].DocumentDate.ToString("MMM/dd/yyyy"));
                rowValues.Add(model.ClosedLoanReportItems[i].AgentName);
                rowValues.Add(model.ClosedLoanReportItems[i].Remarks);
                rowValues.Add(model.ClosedLoanReportItems[i].StatusName);

                table = ReportHelper.SetRowValues(table, row, rowValues);
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.ClosedLoanReportItems[i].Receivable.ToString("#,###,##0.00") }, textAlign: "right");
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.ClosedLoanReportItems[i].TotalPayment.ToString("#,###,##0.00") }, textAlign: "right");
            }

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { string.Empty }, columnSpan: 9, setBorder: false);

            decimal totalReceivable = 0;
            decimal totalPayment = 0;

            if (model.ClosedLoanReportItems.Any())
            {
                totalReceivable = model.ClosedLoanReportItems.Sum(s => s.Receivable);
                totalPayment = model.ClosedLoanReportItems.Sum(s => s.TotalPayment);
            }
                
            var totalRow = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, totalRow, new List<string>() { "TOTAL" }, columnSpan: 7);
            table = ReportHelper.SetHeaderValues(table, totalRow, new List<string>() { totalReceivable.ToString("#,###,##0.00"), totalPayment.ToString("#,###,##0.00") },textAlign: "right");
            
            return table;
        }

        private HtmlTable GetCollectionReconciliationTable(CollectionReconciliationReportModel model)
        {
            var table = ReportHelper.GetTable(setBorder: false);

            table = ReportHelper.SetHeaderValues(table, new HtmlTableRow(), new List<string>() { model.CollectionDate.ToString("MMM/dd/yyyy").ToUpper() + " DAILY COLLECTION RECONCILIATION" }, columnSpan: 12, textAlign: "center");
            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { "" }, setBorder: false, columnSpan: 12);

            var headerRow = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, new HtmlTableRow(), new List<string>() { "SUMMARY" }, textAlign: "center", columnSpan: 12);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Collection Per Record" }, columnSpan: 4);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Actual Cash Collection" }, columnSpan: 4);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Short / Over" }, columnSpan: 4);

            var row = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalCollectionPerRecord.ToString("#,###,##0.00") }, columnSpan: 4);
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalActualCashCollection.ToString("#,###,##0.00") }, columnSpan: 4);
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalShortOver.ToString("#,###,##0.00") }, columnSpan: 4);

            headerRow = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Cash Deposited" }, columnSpan: 2);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Deducted To Cheque - Advance Payment" }, columnSpan: 2);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Deducted To Cheque - Savings" }, columnSpan: 2);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Used for Loan Release" }, columnSpan: 2);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Used in Withdrawal of Savings" }, columnSpan: 2);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total Others" }, columnSpan: 2);

            row = new HtmlTableRow();
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalCashDeposited.ToString("#,###,##0.00") }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalDeductedToChequeAdvancePayment.ToString("#,###,##0.00") }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalDeductedToChequeSavings.ToString("#,###,##0.00") }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalUsedForLoanRelease.ToString("#,###,##0.00") }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalUsedInWithdrawalOfSavings.ToString("#,###,##0.00") }, columnSpan: 2);
            table = ReportHelper.SetRowValues(table, row, new List<string>() { model.TotalOthers.ToString("#,###,##0.00") }, columnSpan: 2);

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { "" }, setBorder: false, columnSpan: 12);

            headerRow = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, new HtmlTableRow(), new List<string>() { "COLLECTION PER RECORD" }, textAlign: "center", columnSpan: 12);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Agent" }, columnSpan: 4);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Posted Payment" }, columnSpan: 4);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Penalty" }, columnSpan: 4);

            for (var i = 0; i < model.CollectionOnRecordItems.Count(); i++)
            {
                row = new HtmlTableRow();
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionOnRecordItems[i].AgentName }, columnSpan: 4);
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionOnRecordItems[i].PostedPayment.ToString("#,###,##0.00")}, textAlign:"right", columnSpan: 4);
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionOnRecordItems[i].Penalty.ToString("#,###,##0.00") }, textAlign: "right", columnSpan: 4);
            }

            table = ReportHelper.SetRowValues(table, new HtmlTableRow(), new List<string>() { "" }, setBorder: false, columnSpan: 12);

            headerRow = new HtmlTableRow();
            table = ReportHelper.SetHeaderValues(table, new HtmlTableRow(), new List<string>() { "ACTUAL CASH COLLECTION" }, textAlign: "center", columnSpan: 12);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Agent" }, columnSpan: 2);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Cash Deposited" });
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Deducted To Cheque - Advance Payment" });
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Deducted To Cheque - Savings" });
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Used for Loan Release" });
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Used in Withdrawal of Savings" });
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Others" });
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Remarks" }, columnSpan: 2);
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Total" });
            table = ReportHelper.SetHeaderValues(table, headerRow, new List<string>() { "Status" });

            for (var i = 0; i < model.CollectionReconciliationItems.Count(); i++)
            {
                row = new HtmlTableRow();
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].AgentName }, columnSpan: 2);
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].CashDeposited.ToString("#,###,##0.00") }, textAlign: "right");
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].DeductedToChequeAdvancePayment.ToString("#,###,##0.00") }, textAlign: "right");
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].DeductedToChequeSavings.ToString("#,###,##0.00") }, textAlign: "right");
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].UsedForLoanRelease.ToString("#,###,##0.00") }, textAlign: "right");
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].UsedInWithdrawalOfSavings.ToString("#,###,##0.00") }, textAlign: "right");
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].Others.ToString("#,###,##0.00") }, textAlign: "right");
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].Remarks }, columnSpan: 2);
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].Total.ToString("#,###,##0.00") }, textAlign: "right");
                table = ReportHelper.SetRowValues(table, row, new List<string>() { model.CollectionReconciliationItems[i].Status });
            }

            return table;
        }
        
        #endregion
    }
}
